package soft.quicknote;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.ArrayList;
import java.util.List;

import soft.quicknote.beans.Category;
import soft.quicknote.beans.Note;

public class DBHelper extends SQLiteOpenHelper {
    private final static int VERSION = 1;
    // 数据库名称
    private final static String DB_NAME = "note.db";
    // 表名
    public final static String NOTE_TABLE = "note";
    public final static String CAT_TABLE = "category";

    // 列名
    public final static String[] COLUMN_NAMES = new String[]{"id", "title", "content", "updateTime"};

    // 建立Note表的SQL语句
    private final static String CREATE_NOTE_TBL = "create table note(id integer primary key autoincrement, title text, content text, updateTime integer, tag integer, category integer)";

    private final static String CREATE_CATEGORY_TBL = "create table category(id integer primary key autoincrement, name text)";

    private SQLiteDatabase db;

    public DBHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {

        //调用父类的构造函数
        super(context, name, factory, version);
    }

    public DBHelper(Context context, String name, int version) {

        //调用具有相同形参的构造方法
        this(context, name, null, version);
    }


    public DBHelper(Context context) {
        this(context, DB_NAME, null, VERSION);
    }


    @Override
    public void onCreate(SQLiteDatabase db) {
        this.db = db;
        System.out.println("Create Database");
        //调用execSQL方法操作数据库，建立note表
        db.execSQL(CREATE_NOTE_TBL);
        db.execSQL(CREATE_CATEGORY_TBL);

        db.insert(CAT_TABLE, null, defaultCategory());

    }


    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        System.out.println("update Database");
    }

    /**
     * 插入便签
     *
     * @param note
     */
    public int insertNote(Note note) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = this.valuesFromNote(note);
        values.remove("id");
        db.insert(NOTE_TABLE, null, values);
        Cursor cursor = db.rawQuery(String.format("SELECT * FROM %s ORDER BY id desc", NOTE_TABLE), null);
        cursor.moveToFirst();
        int id = cursor.getInt(0);
        cursor.close();
        db.close();
        return id;
    }


    /**
     * 通过id获取对应的便签
     *
     * @param id
     * @return Note对象
     */
    public Note getNoteById(int id) {
        SQLiteDatabase db = getReadableDatabase();
        //对象c=每行的集合
        Cursor c = db.query(NOTE_TABLE, null, "id=?", new String[]{Integer.toString(id)}, null, null, null, null);
        //如果第一行为空，则返回null
        if (!c.moveToFirst()) {
            return null;
        }
        return getNoteFromCursor(c);
    }

    /**
     * 将便签保存到数据库
     *
     * @param note
     * @return 保存成功返回true
     */
    public boolean saveNote(Note note) {
        SQLiteDatabase db = getWritableDatabase();

        ContentValues values = this.valuesFromNote(note);
        //对数据库进行更新的操作
        db.update(NOTE_TABLE, values, "id=?", new String[]{String.valueOf(note.getId())});
        db.close();
        return true;
    }

    /**
     * 请求所有标签
     *
     * @return 便签列表
     */
    public List<Note> queryAllNotes() {
        Cursor cursor = this.queryAllNote();
        //moveToFirst() 定位表中的第一行
        cursor.moveToFirst();
        ;
        ArrayList<Note> list = new ArrayList<>();
        //isAfterLast() ——返回游标是否指向第最后一行的位置
        while (!cursor.isAfterLast()) {
            list.add(getNoteFromCursor(cursor));
            //moveToNext --移动游标至下一行
            cursor.moveToNext();
        }
        cursor.close();
        return list;
    }

    /**
     * 根据id删除对应的便签
     *
     * @param id
     * @return 便签删除成功
     */
    public boolean deleteNote(int id) {
        SQLiteDatabase db = getWritableDatabase();
        int row = db.delete(NOTE_TABLE, "id=?", new String[]{String.valueOf(id)});
        db.close();
        //如果row>0则证明有删改
        return row > 0;
    }

    public Cursor queryAllNote() {
        //getReadableDatabase()方法则是先以读写方式打开数据库，如果数据库的磁盘空间满了，就会打开失败，当打开失败后会继续尝试以只读方式打开数据库。
        SQLiteDatabase db = getReadableDatabase();
        Cursor c = db.query(NOTE_TABLE, null, null, null, null, null, null, null);
        return c;
    }

    public void update(ContentValues values, String whereClause, String[] whereArgs) {
        SQLiteDatabase db = getWritableDatabase();
        db.update(NOTE_TABLE, values, whereClause, whereArgs);
        db.close();
    }

    public void close() {
        if (db != null) {
            db.close();
        }
    }

    /**
     * 将便签插入到数据库
     *
     * @param values
     */
    public void insertNote(ContentValues values) {
        SQLiteDatabase db = getWritableDatabase();
        db.insert(NOTE_TABLE, null, values);
        db.close();
    }


    public Category insertCategory(Category category) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("name", category.getName());
        db.insert(CAT_TABLE, null, values);
        int id = getMaxId(db, CAT_TABLE);
        category.setId(id);
        db.close();
        return category;
    }

    public String querySingleCategory(int id) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor c = db.query(CAT_TABLE, null, "id=?", new String[]{Integer.toString(id)}, null, null, null);
        if (!c.moveToFirst()) return null;
        return c.getString(1);
    }


    public boolean deleteSingleCategory(int id) {
        SQLiteDatabase db = getWritableDatabase();
        int cnt = db.delete(CAT_TABLE, "id=?", new String[]{String.valueOf(id)});
        return cnt > 0;
    }

    public boolean saveManyCategories(List<Category> cats) {
        SQLiteDatabase db = getWritableDatabase();

        for (Category cat : cats) {
            ContentValues values = new ContentValues();
            values.put("name", cat.getName());
            //对数据库进行更新的操作
            db.update(CAT_TABLE, values, "id=?", new String[]{String.valueOf(cat.getId())});
        }
        db.close();
        return true;
    }

    public ArrayList<Category> queryAllCategory() {
        ArrayList<Category> res = new ArrayList<>();

        SQLiteDatabase db = getReadableDatabase();
        Cursor c = db.query(CAT_TABLE, null, null, null, null, null, null, null);
        if (!c.moveToFirst()) return res;
        while (!c.isAfterLast()) {
            Category cat = new Category(-1, "");
            cat.setId(c.getInt(0));
            cat.setName(c.getString(1));
            res.add(cat);
            c.moveToNext();
        }
        return res;
    }

    /**
     * 从 Cursor 获得 Note 对象
     *
     * @param cursor
     * @return Note
     */
    public static Note getNoteFromCursor(Cursor cursor) {
        return new Note(
                cursor.getInt(0),
                cursor.getString(1),
                cursor.getString(2),
                cursor.getLong(3),
                cursor.getInt(4),
                cursor.getInt(5));
    }

    /**
     * 根据 Note 获得 ContentValues
     *
     * @param note
     * @return vlaues
     */
    private ContentValues valuesFromNote(Note note) {
        ContentValues values = new ContentValues();
        values.put("id", note.getId());
        values.put("content", note.getContent());
        values.put("title", note.getTitle());
        values.put("tag", note.getTag());
        values.put("updateTime", note.getUpdateTime());
        values.put("category", note.getCategory());
        return values;
    }

    private static ContentValues defaultCategory() {
        ContentValues values = new ContentValues();
        values.put("name", "默认");
        return values;
    }

    private int getMaxId(SQLiteDatabase db, String tableName) {
        Cursor cursor = db.rawQuery(String.format("SELECT max(id) FROM %s", tableName), null);
        cursor.moveToNext();
        int id = cursor.getInt(0);
        cursor.close();
        return id;
    }
}